﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ZHDJ.Data.Models
{
    public class PartyATMSaveAndTake : EntityBase
    {
        #region 字段属性

        /// <summary>
        /// 
        /// </summary>
        public string SaveID { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public string TakeID { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public int Status { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public bool? IsSub { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public string Description { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public string Createby { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public string BranchID { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public DateTime? CreateTime { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public int IsSync { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public int AdminAudit { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public int UserAudit { get; set; }

        #endregion

        #region 数据绑定
        /// <summary>
        /// 捐助
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="name"></param>
        /// <param name="createBy"></param>
        /// <param name="branchID"></param>
        /// <param name="titular"></param>
        /// <param name="thingType"></param>
        /// <param name="purposes"></param>
        /// <param name="saveModus"></param>
        /// <param name="status"></param>
        /// <param name="createby"></param>
        /// <param name="IsLoveActivity"></param>
        /// <returns></returns>
        public static DataSet GetGridData(int pageIndex, int pageSize, string name, string createBy, string branchID, string titular,
            string thingType, string purposes, string saveModus, string status, string createby, string IsLoveActivity = "0", bool isAppeal = false)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "Begin",
                    Value = (pageIndex)*pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "PageSize",
                    Value = pageSize
                }
            };
            StringBuilder sbSql = new StringBuilder();

            //            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
            //            {
            //                StringBuilder sbSql = new StringBuilder();
            //                sbSql.Append(@"SELECT Data.*,
            //ST.ParaMemberName as TitularName,STC.ParaMemberName as ThingTypeName,
            //(CASE data.IsEndTime WHEN 1 THEN '是' ELSE '否' END) as IsEndTimeName,
            //(CASE data.Purposes WHEN 2 THEN Data.PurposesDescription ELSE SP.ParaMemberName END) as PurposesName,
            //SSM.ParaMemberName as SaveModusName,
            //OM.UserName as CreateByName,OB.BranchName as CreateByBranchName,
            //(CASE data.IsSub WHEN 1 THEN '是' ELSE '否' END) as IsSubName,
            //SS.ParaMemberName as StatusName 
            //FROM tblPartyATMSaveThings Data
            //left join tblPartyOrgMembers OM on OM.LoginID = Data.CreateBy
            //left join tblPartyOrgBranch OB on OB.BranchID = Data.BranchID and OB.BranchID =OM.BranchID
            //left join tblSysSystemParameter ST on ST.ParaMember = Data.Titular and ST.ParaType = 'SXT.ATM.Titular'
            //left join tblSysSystemParameter STC on STC.ParaMember = Data.ThingType and STC.ParaType = 'SXT.ATM.ThingType'
            //left join tblSysSystemParameter SP on SP.ParaMember = Data.Purposes and SP.ParaType = 'SXT.ATM.Purposes'
            //left join tblSysSystemParameter SSM on SSM.ParaMember = Data.SaveModus and SSM.ParaType = 'SXT.ATM.SaveModus'
            //left join tblSysSystemParameter SS on SS.ParaMember = Data.Status and SS.ParaType = 'SXT.ATM.SaveStatus'
            //where 1 = 1  ");

            sbSql.AppendFormat(@"SELECT SQL_CALC_FOUND_ROWS  Data.*,
ST.DDicMemberName as TitularName,STC.DDicMemberName as ThingTypeName,
IF(data.IsEndTime=1,'是','否') as IsEndTimeName,
-- (CASE data.IsEndTime WHEN 1 THEN '是' ELSE '否' END) as IsEndTimeName,
IF(data.Purposes=2,Data.PurposesDescription,SP.DDicMemberName) as PurposesName,
-- (CASE data.Purposes WHEN 2 THEN Data.PurposesDescription ELSE SP.ParaMemberName END) as PurposesName,
SSM.DDicMemberName as SaveModusName,
OM.UserName as CreateByName,OB.BranchName as CreateByBranchName,
IF(Data.isSub=1,'是','否') as IsSubName,
SS.DDicMemberName as StatusName 
FROM {0} Data
left join tblpartyorgmember OM on OM.LoginID = Data.CreateBy
left join tblPartyOrgBranch OB on OB.BranchID = Data.BranchID and OB.BranchID =OM.BranchID
left join tbldatadictionary ST on ST.DDicMember = Data.Titular and ST.DDicType = 'ZHDJ.ATM.Titular'
left join tbldatadictionary STC on STC.DDicMember = Data.ThingType and STC.DDicType = 'ZHDJ.ATM.ThingType'
left join tbldatadictionary SP on SP.DDicMember = Data.Purposes and SP.DDicType = 'ZHDJ.ATM.Purposes'
left join tbldatadictionary SSM on SSM.DDicMember = Data.{1} and SSM.DDicType = 'ZHDJ.ATM.SaveModus'
left join tbldatadictionary SS on SS.DDicMember = Data.Status and SS.DDicType = 'ZHDJ.ATM.TakeStatus'
where 1 = 1 ", isAppeal ? "tblPartyATMTakeThings" : "tblPartyATMSaveThings", isAppeal ? "TakeModus" : "SaveModus");
            if (IsLoveActivity.Equals("1"))
            {
                sbSql.Append(" and Data.IsLoveActivity = 1 ");
            }
            else
            {
                sbSql.Append(" and (Data.IsLoveActivity = 0 or Data.IsLoveActivity is null) ");
            }
            if (!string.IsNullOrEmpty(name))
            {
                sbSql.Append(" and Data.Name like ?Name ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "Name", Value = string.Format("%{0}%", name) });
            }
            if (!string.IsNullOrEmpty(createby))
            {
                sbSql.Append(" and Data.createby like ?createby1 ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "createby1", Value = string.Format("%{0}%", createby) });
            }
            if (!string.IsNullOrEmpty(branchID))
            {
                sbSql.Append(" and Data.BranchID in (select ID from f_BranchID(dbo.QUERY_SECOND_GRADE_BRANCH(?BranchID)))  ");
                parameters.Add(
                   new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchID });
            }
            else
            {
                if (!string.IsNullOrEmpty(createBy))
                {
                    sbSql.Append(" and Data.CreateBy = ?CreateBy ");
                    parameters.Add(
                   new MySqlParameter() { DbType = DbType.String, ParameterName = "CreateBy", Value = createBy });
                }
            }
            if (!string.IsNullOrEmpty(titular))
            {
                sbSql.Append(" and Data.Titular = ?Titular ");
                parameters.Add(
                   new MySqlParameter() { DbType = DbType.String, ParameterName = "Titular", Value = titular });
            }
            if (!string.IsNullOrEmpty(thingType))
            {
                sbSql.Append(" and Data.ThingType = ?ThingType ");
                parameters.Add(
                   new MySqlParameter() { DbType = DbType.String, ParameterName = "ThingType", Value = thingType });
            }
            if (!string.IsNullOrEmpty(purposes))
            {
                sbSql.Append(" and Data.Purposes = ?Purposes ");
                parameters.Add(
                   new MySqlParameter() { DbType = DbType.String, ParameterName = "Purposes", Value = purposes });
            }
            if (!string.IsNullOrEmpty(saveModus))
            {
                if (isAppeal)
                {
                    sbSql.Append(" and Data.TakeModus = ?SaveModus ");
                }
                else
                {
                    sbSql.Append(" and Data.SaveModus = ?SaveModus ");
                }

                parameters.Add(
                   new MySqlParameter() { DbType = DbType.String, ParameterName = "Purposes", Value = saveModus });
            }
            if (!string.IsNullOrEmpty(status))
            {
                sbSql.Append(" and Data.Status = ?Status ");
                parameters.Add(
                   new MySqlParameter() { DbType = DbType.Int32, ParameterName = "Status", Value = status });
            }
            sbSql.Append(" ORDER BY Data.Status,Data.EndTime DESC,Data.Name LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS(); ");
            return MySqlHelper.ExecuteDataSet(sbSql.ToString(), parameters.ToArray());
        }
    }
    #endregion
}